﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-03-29
修改:
修改时间: 
功能: 转季补货系数
 ******************************************************/

using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using com.nm.library;
using george.Data;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VerticaAPI.model;

namespace VerticaAPI
{
    [Description("转季补货系数")]
    public class DWChangeSeasonRateHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();
        /// Writer：梁家健
        /// Create Date：2017-03-29
        [MustLogin]
        [Description("转季补货系数")]
        [ReturnType(typeof(IList<DWChangeSeasonRateModel.NeceModel>))]
        public ApiResult GetDWChangeSeasonRateList([ParamDescription("零售分部")] string OperationAreaID)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(OperationAreaID)) return Error("零售分部不存在!");
                var dt = ReturnDWChangeSeasonRateList(OperationAreaID);
                var result = ModelConvertHelper<DWChangeSeasonRateModel.NeceModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回转季补货系数
        /// </summary>
        /// <param name="OperationAreaID"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-29
        public DataTable ReturnDWChangeSeasonRateList(string OperationAreaID)
        {
            try
            {
                var sql = $@"select * from ndw.ndw_ChangeSeasonRate where  ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'  and OperationAreaID='{OperationAreaID}'
order by ChangeDate desc";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-29
        [MustLogin]
        [Description("生成转季补货系数")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CreatDWChangeSeasonRate([ParamDescription("零售分部")] string OperationAreaID, [ParamDescription("开始时间")] DateTime? StartDate, [ParamDescription("结束时间")] DateTime? EndDate)
        {
            try
            {
                const string sql = @"DELETE FROM  ndw.ndw_ChangeSeasonRate
WHERE  YearNo={0} AND OperationAreaID='{1}' AND ChangeDate='{2}' and {3}";
                if (string.IsNullOrWhiteSpace(OperationAreaID)) return Error("零售分部不存在!");
                if (!(StartDate.HasValue && EndDate.HasValue)) return Error("请选择时间段!");
                var cmdList = new List<CommandInfo>();
                var list = new List<DWChangeSeasonRateModel.CreatModel>();
                for (var i = 0; i <= (EndDate.Value - StartDate.Value).Days; i++)
                {
                    var cmd = new CommandInfo()
                    {
                        CommandText = string.Format(sql, StartDate.Value.AddDays(i).Year, OperationAreaID, StartDate.Value.AddDays(i).ToString("yyyy-MM-dd"), $" ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' "),
                    };
                    cmdList.Add(cmd);
                    var item = new DWChangeSeasonRateModel.CreatModel()
                    {
                        ExtrSource = UserInfo.UserExtrSource,
                        CompanyID = UserInfo.UserCompanyId,
                        YearNo = StartDate.Value.AddDays(i).Year,
                        OperationAreaID = OperationAreaID,
                        ChangeDate = StartDate.Value.AddDays(i).ToString("yyyy-MM-dd"),
                        NextBeginDate = StartDate.Value.AddDays(i).ToString("yyyy-MM-dd"),
                        NextEndDate = StartDate.Value.AddDays(i + 6).ToString("yyyy-MM-dd"),
                        PastBeginDate = StartDate.Value.AddDays(i - 7).ToString("yyyy-MM-dd"),
                        PastEndDate = StartDate.Value.AddDays(i - 1).ToString("yyyy-MM-dd"),
                    };
                    list.Add(item);
                }
                var dt = DataTableHelper.LinqToDataTable(list);
                Helper.ExecuteSqlTran(cmdList, dt, "ndw.ndw_ChangeSeasonRate");
                return Success(0);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-29
        [JsonType]
        [MustLogin]
        [Description("修改转季补货系数")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdateDWChangeSeasonRate([ParamDescription("列表")] DWChangeSeasonRateModel.ModifyModel param)
        {
            try
            {
                const string sql = @"UPDATE ndw.ndw_ChangeSeasonRate
SET CheckCalcRate={0}
where YearNo={1} AND OperationAreaID='{2}' AND ChangeDate='{3}' and {4}";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText = string.Format(sql, item.CheckCalcRate, item.YearNo, item.OperationAreaID, Convert.ToDateTime(item.ChangeDate).ToString("yyyy-MM-dd"), $" ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' "),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-30
        [JsonType]
        [MustLogin]
        [Description("修改转季补货系数")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModifyDWChangeSeasonRate([ParamDescription("列表")] DWChangeSeasonRateModel.ModifyModel param)
        {
            try
            {
                const string sql = @"UPDATE ndw.ndw_ChangeSeasonRate
SET NextTargetAmount={0}, PastTargetAmount={1}, CalcRate={2}, CheckCalcRate={3}
where YearNo={4} AND OperationAreaID='{5}' AND ChangeDate='{6}' and {7}";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var dt =
                        ReturnOdsErpSDBasShopTargetPersonnelDate(param.OperationAreaName,
                            Convert.ToDateTime(param.list.Min(d => d.PastBeginDate)),
                            Convert.ToDateTime(param.list.Max(d => d.NextEndDate)));
                    var list = ModelConvertHelper<OdsErpSDBasShopTargetPersonnelDateModel.NeceModel>.ConvertToModel(dt);
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var nta =
                            list.Where(
                                d =>
                                    Convert.ToDateTime(d.TargetDate) >= Convert.ToDateTime(item.NextBeginDate) &&
                                    Convert.ToDateTime(d.TargetDate) <= Convert.ToDateTime(item.NextEndDate)).Sum(d => d.TargetMoney);
                        var pta =
                            list.Where(
                                d =>
                                    Convert.ToDateTime(d.TargetDate) >= Convert.ToDateTime(item.PastBeginDate) &&
                                    Convert.ToDateTime(d.TargetDate) <= Convert.ToDateTime(item.PastEndDate)).Sum(d => d.TargetMoney);
                        if (pta == 0) return Error($@"{Convert.ToDateTime(item.PastBeginDate):yyyy-MM-dd}-{Convert.ToDateTime(item.PastEndDate):yyyy-MM-dd}前七天总目标为零");
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(sql, nta, pta, nta / pta, nta / pta, item.YearNo, item.OperationAreaID,
                                    Convert.ToDateTime(item.ChangeDate).ToString("yyyy-MM-dd"), $" ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' "),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回业绩目标表
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-30
        public DataTable ReturnOdsErpSDBasShopTargetPersonnelDate(string OperationAreaName, DateTime StartDate, DateTime EndDate)
        {
            try
            {
                var sql = $@"select sum(TargetMoney) TargetMoney,TargetDate,area.OperationAreaID from nods.nods_erp_SD_Bas_ShopTargetPersonnelDate da
inner join ndw.dw_Shop shop on da.ShopID=shop.ShopID and da.ExtrSource = shop.ExtrSource and da.CompanyID=shop.CompanyID
inner join nods.nods_erp_Bas_OperationArea area on shop.SalePart=area.OperationAreaName and area.ExtrSource = shop.ExtrSource and area.CompanyID=shop.CompanyID
where 
 da.ExtrSource = '{UserInfo.UserExtrSource}' and da.CompanyID='{UserInfo.UserCompanyId}' and 
area.OperationAreaName='{OperationAreaName}' and TargetDate between '{StartDate:yyyy-MM-dd}' and '{EndDate:yyyy-MM-dd}' 
group by TargetDate,area.OperationAreaID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-29
        [JsonType]
        [MustLogin]
        [Description("删除转季补货系数")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult DelDWChangeSeasonRate([ParamDescription("列表")] DWChangeSeasonRateModel.ModifyModel param)
        {
            try
            {
                const string sql = @"DELETE FROM  ndw.ndw_ChangeSeasonRate
WHERE YearNo={0} AND OperationAreaID='{1}' AND ChangeDate='{2}' and {3}";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText = string.Format(sql, item.YearNo, item.OperationAreaID, Convert.ToDateTime(item.ChangeDate).ToString("yyyy-MM-dd"), $" ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' "),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
    }
}
